﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DefaultScope;
using Fabrikam.Phone.ViewModels;
using Microsoft.Synchronization.ClientServices;
using Microsoft.Synchronization.ClientServices.SQLite;

using Windows.Storage;
using SQLiteWinRT;

namespace Fabrikam.Phone.DataModel
{
    public class ContextModel
    {
        // you can test this address if you don't manage to reach your localhost address :) (like me !)
        public static string SyncUri = "http://spertus.cloudapp.net/Fabrikam/DefaultScopeSyncService.svc";

        //public static string SyncUri = "http://169.254.80.80/Fabrikam.Server/DefaultScopeSyncService.svc/";
      
        private DefaultScopeOfflineContext SyncContext { get; set; }

        private static ContextModel current;
        public static ContextModel Current
        {
            get { return current ?? (current = new ContextModel()); }
        }

        public String DatabasePath { get; set; }

        public String DatabaseName { get; set; }

        public ContextModel()
        {
            // Définition du Path
            this.DatabasePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "fabrikamfiber_sqlite.db");
            this.DatabaseName = "fabrikamfiber_sqlite.db";
            this.SyncContext = new DefaultScopeOfflineContext(this.DatabaseName, new Uri(SyncUri, UriKind.Absolute));
            this.SyncContext.CacheController.ControllerBehavior.SerializationFormat = SerializationFormat.ODataJSON;

        }

        public async Task<CacheRefreshStatistics> Sync()
        {
            try
            {

                var result = await this.SyncContext.SynchronizeAsync();

                if (result.Error != null)
                    throw result.Error;

                return result;

            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                return null;
            }
        }

        internal async Task<bool> TableExist(string tableName)
        {
            using (Database db = new Database(ApplicationData.Current.LocalFolder, this.DatabaseName))
            {

                try
                {
                    await db.OpenAsync(SqliteOpenMode.OpenOrCreateReadWrite);

                    const string commandText = "SELECT name FROM sqlite_master WHERE type='table' AND name= ?";

                    using (var sqlCommand = await db.PrepareStatementAsync(commandText))
                    {
                        sqlCommand.BindTextParameterAt(1, tableName);

                        var step = await sqlCommand.StepAsync();

                        if (!step)
                            return false;

                        var tableScope = sqlCommand.GetTextAt(0);

                        return tableScope == tableName;
                    }


                }
                catch (Exception)
                {
                    return false;
                }
                finally
                {
                    // db.Dispose();
                }

            }
        }

        /// <summary>
        /// Check if Database Exist
        /// </summary>
        public async Task<bool> DatabaseExist()
        {
            // Get the isolated storage file for the application.
            StorageFolder applicationFolder = ApplicationData.Current.LocalFolder;

            return await applicationFolder.FileExistsAsync("fabrikamfiber_sqlite.db");
        }


        public async Task<List<ServiceTickets>> GetTickets()
        {
            List<ServiceTickets> serviceTickets = new List<ServiceTickets>();


            using (Database db = new Database(ApplicationData.Current.LocalFolder, this.DatabaseName))
            {
                try
                {
                    await db.OpenAsync(SqliteOpenMode.OpenRead);

                    const string commandText = "SELECT * FROM ServiceTickets";

                    using (var statement = await db.PrepareStatementAsync(commandText))
                    {

                        while (await statement.StepAsync())
                        {
                            ServiceTickets ticket = new ServiceTickets();

                            ticket.ServiceTicketID = Guid.Parse(statement.GetTextAt(0));
                            ticket.Title = statement.GetTextAt(1);
                            ticket.Description = statement.GetTextAt(2);
                            ticket.StatusValue = statement.GetIntAt(3);
                            ticket.EscalationLevel = statement.GetIntAt(4);

                            DateTime op;
                            if (DateTime.TryParse(statement.GetTextAt(5), out op))
                                ticket.Opened = op;

                            DateTime cl;
                            if (DateTime.TryParse(statement.GetTextAt(6), out cl))
                                ticket.Closed = cl;

                            ticket.CustomerID = statement.GetIntAt(7);

                            serviceTickets.Add(ticket);
                        }

                    }

                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }

            }

            return serviceTickets;

        }
        public async Task<List<ServiceTickets>> GetUnclosedTickets()
        {
            List<ServiceTickets> serviceTickets = new List<ServiceTickets>();


            using (Database db = new Database(ApplicationData.Current.LocalFolder, this.DatabaseName))
            {
                try
                {
                    await db.OpenAsync(SqliteOpenMode.OpenRead);

                    const string commandText = "SELECT * FROM ServiceTickets where Closed is null";

                    using (var statement = await db.PrepareStatementAsync(commandText))
                    {

                        while (await statement.StepAsync())
                        {
                            ServiceTickets ticket = new ServiceTickets();

                            ticket.ServiceTicketID = Guid.Parse(statement.GetTextAt(0));
                            ticket.Title = statement.GetTextAt(1);
                            ticket.Description = statement.GetTextAt(2);
                            ticket.StatusValue = statement.GetIntAt(3);
                            ticket.EscalationLevel = statement.GetIntAt(4);
                            ticket.Opened = DateTime.Parse(statement.GetTextAt(5));
                            ticket.Closed = DateTime.Parse(statement.GetTextAt(6));
                            ticket.CustomerID = statement.GetIntAt(7);

                            serviceTickets.Add(ticket);
                        }
                    }


                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }

            }

            return serviceTickets;
        }

        public async Task<List<Customers>> GetCustomers()
        {
            List<Customers> customers = new List<Customers>();


            using (Database db = new Database(ApplicationData.Current.LocalFolder, this.DatabaseName))
            {
                try
                {
                    await db.OpenAsync(SqliteOpenMode.OpenRead);

                    const string commandText = "SELECT * FROM Customers";

                    using (var statement = await db.PrepareStatementAsync(commandText))
                    {

                        while (await statement.StepAsync())
                        {
                            Customers customer = new Customers();

                            customer.ID = statement.GetIntAt(0);
                            customer.FirstName = statement.GetTextAt(2);
                            customer.LastName = statement.GetTextAt(3);
                            customer.Address_Street = statement.GetTextAt(4);
                            customer.Address_City = statement.GetTextAt(5);
                            customer.Address_State = statement.GetTextAt(6);
                            customer.Address_Zip = statement.GetTextAt(7);

                            customers.Add(customer);
                        }
                    }

                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                }

                return customers;

            }
        }



        internal Task SaveItems(ObservableCollection<ServiceTicketViewModel> seviceTickets)
        {
            //return Task.Factory.StartNew(() =>
            //    {
            //        using (SQLiteConnection db = new SQLiteConnection(this.DatabasePath,
            //                                                          SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite))
            //            db.RunInTransaction(() =>
            //                {
            //                    foreach (
            //                        var serviceTicket in seviceTickets.Where(serviceTicket => serviceTicket.IsDirty))
            //                    {
            //                        db.Update(serviceTicket.ServiceTicket);
            //                        serviceTicket.IsDirty = false;
            //                    }
            //                });
            //    });

            return null;
        }


        internal async Task SaveItem(ServiceTicketViewModel serviceTicket)
        {
            //return Task.Factory.StartNew(() =>
            //{
            //    using (SQLiteConnection db = new SQLiteConnection(this.DatabasePath,
            //                                                      SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite))
            //        db.RunInTransaction(() =>
            //        {
            //            serviceTicket.Opened = null;
            //            serviceTicket.Closed = null;

            //            if (serviceTicket.IsNew)
            //                db.Insert(serviceTicket.ServiceTicket);
            //            else
            //                db.Update(serviceTicket.ServiceTicket);

            //            // Update customer addres
            //            db.Update(serviceTicket.Customer);

            //            serviceTicket.IsDirty = false;
            //            serviceTicket.IsNew = false;
            //        });
            //});
        }

        internal async Task DeleteItem(ServiceTicketViewModel serviceTicketViewModel)
        {
            //await Task.Factory.StartNew(() =>
            //{
            //    using (SQLiteConnection db = new SQLiteConnection(this.DatabasePath,
            //                                                      SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite))
            //    {
            //        db.Delete(serviceTicketViewModel.ServiceTicket);
            //    }

            //});
        }


    }

}
